package com.qgqaqgqa.deviceinfomanager.util;


import android.content.ContentResolver;
import android.content.Context;
import android.database.Cursor;
import android.net.Uri;
import android.provider.MediaStore;

import com.qgqaqgqa.deviceinfomanager.model.DeviceInfoModel;
import com.qgqaqgqa.deviceinfomanager.util.utilcode.util.FileIOUtils;
import com.qgqaqgqa.deviceinfomanager.util.utilcode.util.LogUtils;
import com.qgqaqgqa.deviceinfomanager.util.utilcode.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Colour;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * @program: Demo
 * @Date: 2019/2/19 10:50
 * @Author: LiJc
 * @Description:
 */
public class ExcelUtil {

    /**
     * 根据Uri获取真实图片路径
     * <p/>
     * 一个android文件的Uri地址一般如下：
     * content://media/external/images/media/62026
     *
     * @param context
     * @param uri
     * @return
     */
    public static String getRealFilePath(Context context, Uri uri) {
        LogUtils.e("getRealFilePath", "选择的文件Data = " + uri);
        if (null == uri) return null;
        String scheme = uri.getScheme();
        LogUtils.e("getRealFilePath", "选择的文件scheme = " + scheme);
        String data = null;
        if (scheme == null)
            data = uri.getPath();
        else if (ContentResolver.SCHEME_FILE.equals(scheme)) {
            data = uri.getPath();
        } else if (ContentResolver.SCHEME_CONTENT.equals(scheme)) {
            Cursor cursor = context.getContentResolver().query(uri, new String[]{}, null, null, null);

            LogUtils.e("getRealFilePath", "选择的文件cursor.getString(0) = " + cursor.getString(0));
            for(int i=0;i<cursor.getColumnCount();i++){
                LogUtils.e("getRealFilePath", "选择的文件cursor = " + cursor.getColumnName(i));
            }
            if (null != cursor) {
                if (cursor.moveToFirst()) {
                    LogUtils.e("getRealFilePath", "选择的文件cursor = " + cursor.getString(0));
                    int index = cursor.getColumnIndex(MediaStore.Images.ImageColumns.DATA);
                    if (index > -1) {
                        data = cursor.getString(index);
                    }
                }
                cursor.close();
            }
        }
        return data;
    }

//    public static boolean writeExcel(List<Map<String, Object>> list, String path) throws Exception {
//        String postfix = path.substring(path.lastIndexOf(".") + 1, path.length());
//        if (postfix.equals("xls")) {
//            writeXls(list, path);
//        } else if (postfix.equals("xlsx")) {
//            writeXlsx(list, path);
//        }else {
//            System.out.println("文件后缀名不正确！");
//            return false;
//        }
//        return true;
//    }

    /**
     * read the Excel file
     *
     * @param path
     *            the path of the Excel file
     * @return
     * @throws IOException
     */
    public static List<Map<String, String>> readExcel(String path, FileIOUtils.OnProgressUpdateListener listener) throws IOException, BiffException {
        String postfix = path.substring(path.lastIndexOf(".") + 1, path.length());
//        if (postfix.equals("xls")) {
//            return readXls(path);
//        } else if (postfix.equals("xlsx")) {
//            return readXlsx(path);
//        }


//        Workbook book = Workbook.getWorkbook(new File("Test.xls"));
        Workbook book = Workbook.getWorkbook(new File(path));

        //获得第一个工作表对象
//        Sheet sheet = book.getSheet("sheet_one");
//        Sheet sheet = book.getSheet(0);
//
//        int rows = sheet.getRows();
//        int cols = sheet.getColumns();
        double allRows = 0;
        double allCols = 0;
        double curRows = 0;
        for(Sheet sheet:book.getSheets()){
            allRows += sheet.getRows();
            allCols += sheet.getColumns();
        }


//        System.out.println("总列数：" + cols);
//        System.out.println("总行数:" + rows);
//        System.out.println("----------------------------");

        listener.onProgressUpdate(0);

//        int i = 0;
//        int j = 0;
//        //循环读取数据
//        for (i = 0; i < cols; i++) {
//            for (j = 0; j < rows; j++) {
//                System.out.println("第" + j + "行，第" + i + "列为：" + sheet.getCell(i, j).getContents());
//            }
//
//        }
        List<Map<String, String>> list = new ArrayList<Map<String, String>>();
        for (int numSheet = 0; numSheet < book.getNumberOfSheets(); numSheet++) {
            Sheet hssfSheet = book.getSheet(numSheet);
            if (hssfSheet != null&&hssfSheet.getRows()>0){
                long cellCount=hssfSheet.getRow(0).length;
                listener.onProgressUpdate(++curRows / allRows);
                // Read the Row
                for (int rowNum = 1; rowNum < hssfSheet.getRows(); rowNum++) {
                    listener.onProgressUpdate(++curRows / allRows);
                    Cell[] hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow != null) {
                        Map<String, String> map = new HashMap<>();
                        for (int cellNum = 0; cellNum < hssfRow.length; cellNum++) {
                            if(cellCount>cellNum&&hssfSheet.getRow(0)[cellNum]!=null){
                                String key=(hssfSheet.getRow(0)[cellNum].getContents() + "").trim();
                                if(key.toUpperCase().contains("ID")){
                                    key="ID";
                                }

//                        System.out.println("Excel("+numSheet+")第"+rowNum+"行的"+hssfSheet.getRow(0)[cellNum].getContents() +":" + hssfRow[cellNum].getContents());
                                map.put(key, (hssfRow[cellNum].getContents() + "").trim()
                                        .replace(" ","")
                                        .replace("（","(")
                                        .replace("）",")")
                                        .replace("，",",")
                                        .replace("。","."));
                            }
                        }
                        list.add(map);
                    }
                }
            }
        }
        return list;
//        return null;
    }

//    /**
//     * Read the Excel 2010
//     *
//     * @param path
//     *            the path of the excel file
//     * @return
//     * @throws IOException
//     */
//    public static List<Map<String, Object>> readXlsx(String path) throws IOException {
//
//        InputStream is = new FileInputStream(path);
//        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
//        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
//        // Read the Sheet
//        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
//            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
//            if (xssfSheet == null) {
//                continue;
//            }
//            // Read the Row
//            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
//                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
//                if (xssfRow != null) {
//                    Map<String, Object> map = new HashMap<>();
//                    for (int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++) {
//                        if(xssfSheet.getRow(0).getCell(cellNum)==null||xssfRow.getCell(cellNum)==null)
//                            continue;
//                        map.put(xssfSheet.getRow(0).getCell(cellNum) + "", getValue(xssfRow.getCell(cellNum)));
//                    }
//                    list.add(map);
//                }
//            }
//        }
//        xssfWorkbook.close();
//        is.close();
//        return list;
//    }

//    /**
//     * Read the Excel 2003-2007
//     *
//     * @param path
//     *            the path of the Excel
//     * @return
//     * @throws IOException
//     */
//    public static List<Map<String, Object>> readXls(String path) throws IOException {
//        InputStream is = new FileInputStream(path);
//        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
//        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
//        // Read the Sheet
//        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
//            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
//            if (hssfSheet == null) {
//                continue;
//            }
//            // Read the Row
//            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
//                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
//                if (hssfRow != null) {
//                    Map<String, Object> map = new HashMap<>();
//                    for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
//                        if(hssfSheet.getRow(0).getCell(cellNum)==null)
//                            continue;
//                        map.put(hssfSheet.getRow(0).getCell(cellNum) + "", getValue(hssfRow.getCell(cellNum)));
//                    }
//                    list.add(map);
//                }
//            }
//        }
//        hssfWorkbook.close();
//        is.close();
//        return list;
//    }


//    @SuppressWarnings({ "static-access", "deprecation" })
//    private static String getValue(XSSFCell xssfRow) {
//        if (xssfRow == null) {
//            return "";
//        } else if (xssfRow.getCellType() == CellType.BOOLEAN) {
//            return String.valueOf(xssfRow.getBooleanCellValue()).trim();
//        } else if (xssfRow.getCellType() == CellType.NUMERIC) {
//            HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
//            return String.valueOf(dataFormatter.formatCellValue(xssfRow)).trim();
//            // return String.valueOf(xssfRow.getNumericCellValue()).trim();
//        } else {
//            return String.valueOf(xssfRow.getStringCellValue()).trim();
//        }
//    }

//    @SuppressWarnings({ "static-access", "deprecation" })
//    private static String getValue(HSSFCell hssfCell) {
//        if (hssfCell == null) {
//            return "";
//        } else if (hssfCell.getCellType() == CellType.BOOLEAN) {
//            return String.valueOf(hssfCell.getBooleanCellValue()).trim();
//        } else if (hssfCell.getCellType() == CellType.NUMERIC) {
//            HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
//            return String.valueOf(dataFormatter.formatCellValue(hssfCell)).trim();
//            // return String.valueOf(hssfCell.getNumericCellValue()).trim();
//        } else {
//            return String.valueOf(hssfCell.getStringCellValue()).trim();
//        }
//    }

//    public static void writeXls(List<Map<String, Object>> list, String path) throws Exception {
//        if (list == null) {
//            return;
//        }
//        int countColumnNum = list.size();
//        HSSFWorkbook book = new HSSFWorkbook();
//        //	设置样式start
//        HSSFCellStyle cellStyle = setStyleXls(book);
//        //	设置样式end
//        HSSFSheet sheet = book.createSheet("sheet");
//        // option at first row.
//        HSSFRow firstRow = sheet.createRow(0);
//
//        // Set-->数组
//        String[] options = new String[list.get(0).keySet().size()];
//        list.get(0).keySet().toArray(options);
//
//        for (int j = 0; j < options.length; j++) {
//            HSSFCell cell = firstRow.createCell(j);
//            cell.setCellStyle(cellStyle);
//            cell.setCellValue(new HSSFRichTextString(options[j]));
//        }
//
//        for (int i = 0; i < countColumnNum; i++) {
//            HSSFRow row = sheet.createRow(i + 1);
//            Map<String, Object> map = list.get(i);
//            for (int column = 0; column < options.length; column++) {
//                HSSFCell cell = row.createCell(column);
//                cell.setCellStyle(cellStyle);
//                cell.setCellValue((map.get(options[column])==null?"":map.get(options[column])) + "");
//            }
//        }
//        File file = new File(path);
//        if (!file.exists()) {
//            file.mkdirs();
//        }
//        OutputStream os = new FileOutputStream(file);
//        book.write(os);
//        os.close();
//    }

//    public static void writeXlsx(List<Map<String, Object>> list, String path) throws Exception {
//        if (list == null) {
//            return;
//        }
//        // XSSFWorkbook
//        int countColumnNum = list.size();
//        XSSFWorkbook book = new XSSFWorkbook();
//        XSSFCellStyle cellStyle = setStyleXlsx(book);
//        XSSFSheet sheet = book.createSheet("studentSheet");
//
//        // option at first row.
//        XSSFRow firstRow = sheet.createRow(0);
//        // Set-->数组
//        String[] options = new String[list.get(0).keySet().size()];
//        list.get(0).keySet().toArray(options);
//        for (int j = 0; j < options.length; j++) {
//            XSSFCell cell = firstRow.createCell(j);
//            cell.setCellStyle(cellStyle);
//            cell.setCellValue(new XSSFRichTextString(options[j]));
//        }
//        for (int i = 0; i < countColumnNum; i++) {
//            XSSFRow row = sheet.createRow(i + 1);
//            Map<String, Object> map = list.get(i);
//            for (int column = 0; column < options.length; column++) {
//                XSSFCell cell = row.createCell(column);
//                cell.setCellStyle(cellStyle);
//                cell.setCellValue((map.get(options[column])==null?"":map.get(options[column])) + "");
//            }
//        }
//        File file = new File(path);
//        if (!file.exists()) {
//            file.mkdirs();
//        }
//        OutputStream os = new FileOutputStream(file);
//        book.write(os);
//        os.close();
//    }

//    /**
//     * 设置样式
//     * @param book
//     * @return
//     */
//    public static HSSFCellStyle setStyleXls(HSSFWorkbook book){
//        HSSFCellStyle cellStyle = book.createCellStyle();
//        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 居中
//        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直
//        cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
//        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
//        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
//        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
//        return cellStyle;
//    }

//    /**
//     * 设置样式
//     * @param book
//     * @return
//     */
//    public static XSSFCellStyle setStyleXlsx(XSSFWorkbook book){
//        XSSFCellStyle cellStyle = book.createCellStyle();
//        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 居中
//        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直
//        cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
//        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
//        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
//        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
//        return cellStyle;
//    }

    /**
     * 返回文件绝对路径
     *
     * @param subPath
     * @param fileName
     * @return
     */
    public static String getSavePath(String subPath, String fileName) {

        // 处理子路径
        if (!StringUtils.isEmpty(subPath)) {
            subPath += "/";
        } else {
            subPath = "";
        }

        // 处理日期路径
        Date date = new Date();
        SimpleDateFormat yyyyFt = new SimpleDateFormat("yyyy");
        SimpleDateFormat MMFt = new SimpleDateFormat("MM");
        SimpleDateFormat ddFt = new SimpleDateFormat("dd");
        SimpleDateFormat HHFt = new SimpleDateFormat("HH");
        String datePath = MessageFormat.format("{0}/{1}/{2}/{3}/", yyyyFt.format(date), MMFt.format(date), ddFt.format(date),
                HHFt.format(date));

        return "/" + subPath + datePath + getNewFileName(fileName);
    }

    /**
     * 构建新文件名称
     *
     * @param fileName
     * @return
     */
    public static String getNewFileName(String fileName) {
        String[] names = fileName.split("[\\.]");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmSS");
        String dateString = sdf.format(new Date());
        return names[0] + "-" + dateString + "." + names[1];
    }


















    private static WritableFont arial14font = null;

    private static WritableCellFormat arial14format = null;
    private static WritableFont arial10font = null;
    private static WritableCellFormat arial10format = null;
    private static WritableFont arial12font = null;
    private static WritableCellFormat arial12format = null;
    private final static String UTF8_ENCODING = "UTF-8";

    /**
     * 单元格的格式设置 字体大小 颜色 对齐方式、背景颜色等...
     */
    private static void format() {
        try {
            arial14font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
            arial14font.setColour(Colour.LIGHT_BLUE);
            arial14format = new WritableCellFormat(arial14font);
            arial14format.setAlignment(jxl.format.Alignment.CENTRE);
            arial14format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            arial14format.setBackground(Colour.VERY_LIGHT_YELLOW);

            arial10font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
            arial10format = new WritableCellFormat(arial10font);
            arial10format.setAlignment(jxl.format.Alignment.CENTRE);
            arial10format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            arial10format.setBackground(Colour.GRAY_25);

            arial12font = new WritableFont(WritableFont.ARIAL, 10);
            arial12format = new WritableCellFormat(arial12font);
            //对齐格式
            arial10format.setAlignment(jxl.format.Alignment.CENTRE);
            //设置边框
            arial12format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

    /**
     * 初始化Excel
     *
     * @param fileName 导出excel存放的地址（目录）
     * @param colName excel中包含的列名（可以有多个）
     */
    public static void initExcel(String fileName, String[] colName) {
        format();
        WritableWorkbook workbook = null;
        try {
            LogUtils.e("initExcel", fileName);
            File file = new File(fileName);
            if (!file.exists()) {
                file.createNewFile();
            }
            workbook = Workbook.createWorkbook(file);
            //设置表格的名字
            WritableSheet sheet = workbook.createSheet("验证记录", 0);
            //创建标题栏
            sheet.addCell((WritableCell) new Label(0, 0, fileName, arial14format));
            for (int col = 0; col < colName.length; col++  ) {
                sheet.addCell(new Label(col, 0, colName[col], arial10format));
            }
            //设置行高
            sheet.setRowView(0, 340);
            workbook.write();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @SuppressWarnings("unchecked")
    public static <T> void writeObjListToExcel(List<T> objList, String fileName, Context c) {
        format();
        if (objList != null && objList.size() > 0) {
            WritableWorkbook writebook = null;
            InputStream in = null;
            try {
                WorkbookSettings setEncode = new WorkbookSettings();
                setEncode.setEncoding(UTF8_ENCODING);
                in = new FileInputStream(new File(fileName));
                Workbook workbook = Workbook.getWorkbook(in);
                writebook = Workbook.createWorkbook(new File(fileName), workbook);
                WritableSheet sheet = writebook.getSheet(0);

                for (int j = 0; j < objList.size(); j++  ) {
                    DeviceInfoModel m = (DeviceInfoModel) objList.get(j);
                    List<String> list = new ArrayList<>();
                    list.add(m.getName()+"");
                    list.add(m.getId()+"");
                    for (int i = 0; i < list.size(); i ++ ) {
                        sheet.addCell(new Label(i, j +  1, list.get(i), arial12format));
                        if (list.get(i).length() <= 4) {
                            //设置列宽
                            sheet.setColumnView(i, list.get(i).length() +  8);
                        } else {
                            //设置列宽
                            sheet.setColumnView(i, list.get(i).length() +  5);
                        }
                    }
                    //设置行高
                    sheet.setRowView(j + 1, 350);
                }
                writebook.write();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (writebook != null) {
                    try {
                        writebook.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                }
                if (in != null) {
                    try {
                        in.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }

        }
    }
}